﻿--创建数据库FHWineShop
create database FHWineShop
go
--使用数据库FHWineWhop
use FHWineShop
go
--创建管理员表
create table FHAdmin
(
AdminID int primary key identity(1,1), --管理员ID
AdminName nvarchar(50) not null, --管理员名字
AdminPwd nvarchar(20) not null, --管理员密码
AdminRole int not null references FHRole(RoleID), --管理员角色1：经理(超管)；2：客房管理员；3：前台服务员
AdminPhone nvarchar(20), --管理员电话
AdminState nvarchar(1) --管理员状态1：开启；0：关闭
)
--创建角色表
create table FHRole
(
RoleID int primary key identity(1,1),
RoleName nvarchar(10) not null,
)
--向角色表插入数据
insert into FHRole values('经理(超管)')
insert into FHRole values('客房管理员')
insert into FHRole values('前台服务员')
--向管理员表插入数据
insert into FHAdmin values('张峰','123456',1,'110',1)
insert into FHAdmin values('徐丽','123456',2,'110',1)
insert into FHAdmin values('李龙','123456',3,'110',0)
insert into FHAdmin values('王菲','123456',2,'110',1)
select * from FHAdmin
select * from FHRole

--客房表
create table Fhkefang
(
  Num varchar(10) primary key ,   --房间号
  Type varchar(20)  not null  ,    --类型
  area varchar(20)       not null  ,        --面积  
  hour_price  int not null,         --客房费用
  day_price int not null   ,         --一晚价
  ssp     int     not null,           --押金
  state varchar(10) ,                --状态
  rm_num      varchar(10) not null,  --客房管理员
)

insert into Fhkefang values(301,'双人间','30m²',30,200,100,'已预订','徐丽')
insert into Fhkefang values(302,'单人间','15m²',30,150,100,'未预订','王菲')
insert into Fhkefang values(303,'单人间','15m²',30,150,100,'已预订','王菲')
insert into Fhkefang values(304,'豪华总统套房','100m²',200,20000,10000,'已预订','徐丽')
insert into Fhkefang values(305,'大床房','30m²',30,300,100,'未预订','王菲')
insert into Fhkefang values(306,'临时房','30m²',30,200,100,'未预订','徐丽')
--预定表
create table YDhouse
(
Id int primary key identity(1,1),--用户id
Num varchar(50) not null,--顾客身份证
)
insert into YDhouse values('411671199709081723')
insert into YDhouse values('411671199012201628')
insert into YDhouse values('411671199608101613')
insert into YDhouse values('411671199207081761')
insert into YDhouse values('411671199509231722')

select * from YDhouse

--入住表
create table RZhouse
(
Id int primary key identity(1,1),--用户id
Num varchar(50)  not null, ----顾客身份证
)
insert into RZhouse values('411671199709081723')
insert into RZhouse values('411671199012201628')
insert into RZhouse values('411671199608101613')
insert into RZhouse values('411671199207081761')
insert into RZhouse values('411671199509231722')

select * from RZhouse


  --顾客表
  create table GKB
(
	num int primary key identity(1,1), ---身份证号
	name varchar(10) not null,         ---姓名
	room_num  int not null,            ---预定房间号
	intype varchar(20) not null,       ---预定房间类型
	intime varchar(10) not null,       ---入住时长
	or_time date not null,             ---预定时长
	in_time date not null,             ---预定入住时间
	co_time date not null,             ---预定退房时间
	fore_num varchar(10) not null,     ---前台接待员
)
insert into GKB values('暖洋洋',301,'双人间','24H','2020-12-18-14:00','2020-12-18-14:00','2020-12-19-12:00','李龙')
insert into GKB values('喜洋洋',302,'单人间','24H','2020-12-18-14:00','2020-12-18-14:00','2020-12-19-12:00','李龙')
insert into GKB values('懒洋洋',303,'单人间','24H','2020-12-18-14:00','2020-12-18-14:00','2020-12-19-12:00','李龙')
insert into GKB values('沸洋洋',304,'豪华总统套房','24H','2020-12-18-14:00','2020-12-18-14:00','2020-12-19-12:00','李龙')

insert into GKB values('美洋洋',305,'大床房','24H','2020-12-18-14:00','2020-12-18-14:00','2020-12-19-12:00','李龙')
insert into GKB values('灰太狼',306,'临时房','24H','2020-12-18-14:00','2020-12-18-14:00','2020-12-19-12:00','李龙')

select * from GKB
